const connection = require('../../database/connection')

module.exports = {
    // 包管理
    async getPackagesTotal(data) {
        const [[total]] = await connection.execute('SELECT COUNT(*) total FROM `packages` p, `centers` c, `departments` d, `promotion_channels` pc WHERE p.`time` LIKE "' + data + '%" AND c.id = p.center_id AND d.id = p.department_id AND pc.id = p.promotion_id AND p.`deleted_at` IS NULL AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL AND pc.`deleted_at` IS NULL;')
        return total
    },
    async getPackages(data) {
        const [packages] = await connection.execute('SELECT p.`id`, p.`time`, p.`created_at`, p.`cost`, p.`amount`, c.`name` center_name, d.`name` dept_name, pc.`name` promotion_name FROM `packages` p, `centers` c, `departments` d, `promotion_channels` pc WHERE p.`time` LIKE "' + data.search + '%" AND c.id = p.center_id AND d.id = p.department_id AND pc.id = p.promotion_id AND p.`deleted_at` IS NULL AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL AND pc.`deleted_at` IS NULL ORDER BY p.id LIMIT ' + data.limit + ' OFFSET ' + data.offset + ';')
        return packages
    },
    async addPackage(data) {
        const [_package] = await connection.execute('INSERT INTO `packages` (`promotion_id`, `time`, `cost`, `amount`, `center_id`, `department_id`, `created_at`) VALUES ("' + data.promotion_id + '", "' + data.time + '", "' + data.cost + '", "' + data.amount + '", "' + data.center_id + '", "' + data.dept_id + '", NOW());')
        return _package
    },
    async getPackageById(id) {
        const [[_package]] = await connection.execute('SELECT p.* FROM `packages` p WHERE p.`id` = "' + id + '" AND p.`deleted_at` IS NULL;')
        return _package
    },
    async updatePackage(data) {
        const [promotion] = await connection.execute('UPDATE `packages` p SET p.`time` = "' + data.time + '", p.`promotion_id` = "' + data.promotion_id + '", p.`cost` = "' + data.cost + '", p.`amount` = "' + data.amount + '", p.`center_id` = "' + data.center_id + '", p.`department_id` = "' + data.dept_id + '", p.`updated_at` = NOW() WHERE p.`id` = "' + data.id + '";')
        return promotion
    },
    async rmPackageById(id) {
        const [_package] = await connection.execute('UPDATE `packages` p SET p.`deleted_at` = NOW() WHERE p.`id` = "' + id + '";')
        return _package
    },

};

